package com.crm.dao.permission;

import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.crm.pojo.Role;

public interface RoleDAO extends CrudRepository<Role, Long> {

	@Query("from Role r where r.rstatus = '是' order by r.id asc ")
	public List<Role> queryAll();
	
	@Query("from Role r where r.rstatus = '是' order by r.id desc ")
	public List<Role> queryRoleAll(Pageable pageable);
	
	@Query(value="select count(*) from Role r where r.rstatus = '是' ")
	public Long queryRoleCount();
	
	@Query(value="select r.rname from Role r where r.rstatus = '是' and r.id = ?1 ")
	public String queryRoleName(Long rid);
	
	@Query(nativeQuery = true,value="select r.RNAME from role r "
			+ " JOIN USER_ROLE ur on r.rid = ur.RID "
			+ " JOIN USERINFO u on u.U_ID = ur.U_ID where u.U_ID = ?1")
	public String queryRoleNameUser(Long uId);
	
	@Query(value="select count(*) from Role r where r.rstatus = '是' and r.rname like ?1 ")
	public Long queryRoleNameCount(String rname);
	
	@Query(" from Role r where r.rname = ?1 and r.rstatus = '是' ")
	public Role queryOneName(String rname);
	
	@Query("from Role r where r.rname like ?1 and r.rstatus = '是' order by r.id desc ")
	public List<Role> quertRoleName(String rname,Pageable pageable);
	
	@Query(nativeQuery = true,value=" SELECT RID, RNAME,(case when RID in "
			+ " (SELECT RID from USER_ROLE where U_ID = ?1) then 1 else 0 end) checked " 
            +" from role where RSTATUS = '是' ")
	public List<Object[]> queryForUserGrantRole(Long uId);
	
} 